<?php
require_once 'libs/auth.class.php';
require_once 'libs/mysql.class.php';
require_once 'libs/menu.class.php';

$conn = new Mysql();
$auth = new Auth();

if ( !$auth->is_connected() ) {
	header('Location: login.php');
	exit();
}

require_once 'libs/Twig/Autoloader.php';
Twig_Autoloader::register();

$loader = new Twig_Loader_Filesystem('templates');
$twig = new Twig_Environment($loader, array(
		/*'cache' => '/path/to/compilation_cache',*/
));

$tplVars = array();

$tplVars['breadcrumb'][] = array(
        'caption' => 'Dashboard',
        'href' => 'index.php',
        'curr' => false,
    );

$tplVars['breadcrumb'][] = array(
		'caption' => 'Cabinet',
		'href' => 'cabinet.php',
		'curr' => false,
);

$tplVars['breadcrumb'][] = array(
		'caption' => 'New',
		'href' => 'cabinet.php',
		'curr' => true,
);

$tplVars['jsFiles'] = array(
	'bootstrap/js/jquery.min.js',
    'bootstrap/js/jquery.ui.custom.js',
    'bootstrap/js/bootstrap.min.js',
    'bootstrap/js/jquery.uniform.js',
    'bootstrap/js/jquery.dataTables.min.js',
    'bootstrap/js/select2.min.js',
	'bootstrap/js/unicorn.js',
	'bootstrap/js/unicorn.tables.js',
    
    
    
    'bootstrap/js/excanvas.min.js',
    
    
    'bootstrap/js/jquery.flot.min.js',
    'bootstrap/js/jquery.flot.resize.min.js',
    
    
);





$action = 'list';
$tableName = 'consultation';
$tplVars['tableName'] = $tableName;

if ( $action == 'list' ) {
    
    $template = 'consult-list.twig';
    $sql = "SELECT c.id, CONCAT(p.nom,' ', p.prenom) AS Patient, DATE_FORMAT(c.date, '%e-%c-%Y') AS date, c.montant, mp.label AS ModePaiement, c.payer, c.comment
		FROM consultation c, patient p, mode_paiement mp
		WHERE c.id_patient=p.id
		AND c.id_mode_paiement=mp.id
        ORDER BY c.date";

    $array = $conn->QueryArray($sql, MYSQL_ASSOC);
    
    $tplVars['columns'] = array(/*'id',*/ 'Patient', 'date', 'montant', 'ModePaiement', 'payer', 'comment');
    $tplVars['rows']    = $conn->QueryArray($sql, MYSQL_ASSOC);
    
    
    /*
     * Chart
     */
    $sql = "SELECT MONTH(date) as month,YEAR(date) as year
			FROM `consultation` 
			GROUP BY YEAR(date), MONTH(date)
            ORDER BY YEAR(date), MONTH(date)";
    $res = $conn->QueryArray($sql, MYSQL_ASSOC);
    foreach( $res as $row ) {
        
        $sql_report = "SELECT SUM(montant) as total, COUNT(id) as count, MONTH(date) as month, YEAR(date) as year
                        FROM `consultation`
                        WHERE MONTH(date) = '".$row['month']."'
                        AND YEAR(date)	 = '".$row['year']."'";
        $tmp = $conn->QueryArray($sql_report, MYSQL_ASSOC);
        $res_report[] = array(
            'total' => $tmp[0]['total'],
            'count' => $tmp[0]['count'],
            'month' => $tmp[0]['month'],
            'year' => $tmp[0]['year'],
            'time' => strtotime($tmp[0]['year'].'/'.$tmp[0]['month'].'/01'),
            
            );
        // number_format($row2['total_soins'], 2, ',', ' ')
    }   
}
$tplVars['datasChart'] = $res_report;
//echo "<pre>";
//print_r($res_report);
//exit;
$template = 'consult-report.twig';


$menu = new Menu();
$menu->setCurrentMenu('Consultation');
$tplVars['menus']     = $menu->getMenu();
$tplVars['curr_menu'] = $menu->getCurrentMenu();

echo $twig->render($template, $tplVars);